--
-- PostgreSQL database dump
--

-- Dumped from database version 9.0.1
-- Dumped by pg_dump version 9.0.1
-- Started on 2011-07-18 13:20:53

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 330 (class 2612 OID 11574)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 326 (class 1247 OID 42826)
-- Dependencies: 5
-- Name: rainbow; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE rainbow AS ENUM (
    'red',
    'orange',
    'yellow',
    'green',
    'blue',
    'purple'
);


ALTER TYPE public.rainbow OWNER TO postgres;

--
-- TOC entry 21 (class 1255 OID 41106)
-- Dependencies: 5 330
-- Name: getfio(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getfio(fname character varying, lname character varying, mname character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$BEGIN
return lname || ' ' || COALESCE(substring(fname,1,1) || '. ' ,'')  || 
		       COALESCE(substring(mname,1,1) || '.' ,'');
END;$$;


ALTER FUNCTION public.getfio(fname character varying, lname character varying, mname character varying) OWNER TO postgres;

--
-- TOC entry 20 (class 1255 OID 42854)
-- Dependencies: 330 5
-- Name: getfullfio(character varying, character varying, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getfullfio(fname character varying, lname character varying, mname character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$BEGIN
return lname || ' ' || COALESCE(fname || ' ','')  || COALESCE(mname,'');
END;$$;


ALTER FUNCTION public.getfullfio(fname character varying, lname character varying, mname character varying) OWNER TO postgres;

--
-- TOC entry 18 (class 1255 OID 42835)
-- Dependencies: 330 5
-- Name: getorderstate(date, date); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getorderstate(expected_date date, actual_date date) RETURNS integer
    LANGUAGE plpgsql
    AS $$
BEGIN 

IF expected_date IS NULL THEN 
	RETURN 1; 	  --Новая 
END IF;

IF actual_date IS NULL THEN
	IF expected_date >= CURRENT_DATE THEN 
    	RETURN 2; --В работе 
    ELSE
    	RETURN 5; --Просрочена    
    END IF; 
ELSE 
	IF actual_date > expected_date THEN
    	RETURN 4; --Не в срок
    ELSE
    	RETURN 3; --Выполнена
    END IF;
 END IF;    
 
END;
$$;


ALTER FUNCTION public.getorderstate(expected_date date, actual_date date) OWNER TO postgres;

--
-- TOC entry 19 (class 1255 OID 42848)
-- Dependencies: 5 330
-- Name: getstatename(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION getstatename(statekey integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $$DECLARE result varchar(20);
BEGIN
SELECT INTO result 
        CASE statekey
             WHEN  1 THEN 'Новая'
             WHEN  2 THEN 'В работе'
             WHEN  3 THEN 'Выполнена'
             WHEN  4 THEN 'Не в срок'
             WHEN  5 THEN 'Просрочена'  
         END;               
  RETURN result;
END;
$$;


ALTER FUNCTION public.getstatename(statekey integer) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1523 (class 1259 OID 39705)
-- Dependencies: 1812 5
-- Name: code; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE code (
    id integer NOT NULL,
    code character varying(10) NOT NULL,
    name character varying(255) NOT NULL,
    shortname character varying(150) NOT NULL,
    "order" integer DEFAULT 0
);


ALTER TABLE public.code OWNER TO postgres;

--
-- TOC entry 1524 (class 1259 OID 39714)
-- Dependencies: 1814 5
-- Name: code_href; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE code_href (
    id integer NOT NULL,
    code_id integer NOT NULL,
    parent_id integer DEFAULT 0
);


ALTER TABLE public.code_href OWNER TO postgres;

--
-- TOC entry 1525 (class 1259 OID 39717)
-- Dependencies: 5 1524
-- Name: code_href_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE code_href_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.code_href_id_seq OWNER TO postgres;

--
-- TOC entry 1849 (class 0 OID 0)
-- Dependencies: 1525
-- Name: code_href_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE code_href_id_seq OWNED BY code_href.id;


--
-- TOC entry 1850 (class 0 OID 0)
-- Dependencies: 1525
-- Name: code_href_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('code_href_id_seq', 76, true);


--
-- TOC entry 1522 (class 1259 OID 39703)
-- Dependencies: 5 1523
-- Name: code_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE code_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.code_id_seq OWNER TO postgres;

--
-- TOC entry 1851 (class 0 OID 0)
-- Dependencies: 1522
-- Name: code_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE code_id_seq OWNED BY code.id;


--
-- TOC entry 1852 (class 0 OID 0)
-- Dependencies: 1522
-- Name: code_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('code_id_seq', 99, true);


--
-- TOC entry 1529 (class 1259 OID 41027)
-- Dependencies: 5
-- Name: responsible; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE responsible (
    id integer NOT NULL,
    lastname character varying(100) NOT NULL,
    firstname character varying(100),
    middlename character varying(100),
    phone character varying(100),
    note character varying(255)
);


ALTER TABLE public.responsible OWNER TO postgres;

--
-- TOC entry 1853 (class 0 OID 0)
-- Dependencies: 1529
-- Name: COLUMN responsible.lastname; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN responsible.lastname IS 'Фамилия';


--
-- TOC entry 1854 (class 0 OID 0)
-- Dependencies: 1529
-- Name: COLUMN responsible.firstname; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN responsible.firstname IS 'Имя';


--
-- TOC entry 1855 (class 0 OID 0)
-- Dependencies: 1529
-- Name: COLUMN responsible.middlename; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN responsible.middlename IS 'Отчество';


--
-- TOC entry 1856 (class 0 OID 0)
-- Dependencies: 1529
-- Name: COLUMN responsible.note; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN responsible.note IS 'Примечание';


--
-- TOC entry 1528 (class 1259 OID 41025)
-- Dependencies: 5 1529
-- Name: responsible_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE responsible_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.responsible_id_seq OWNER TO postgres;

--
-- TOC entry 1857 (class 0 OID 0)
-- Dependencies: 1528
-- Name: responsible_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE responsible_id_seq OWNED BY responsible.id;


--
-- TOC entry 1858 (class 0 OID 0)
-- Dependencies: 1528
-- Name: responsible_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('responsible_id_seq', 31, true);


--
-- TOC entry 1527 (class 1259 OID 39763)
-- Dependencies: 5
-- Name: review; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE review (
    id integer NOT NULL,
    name character varying(255) NOT NULL
);


ALTER TABLE public.review OWNER TO postgres;

--
-- TOC entry 1526 (class 1259 OID 39761)
-- Dependencies: 1527 5
-- Name: review_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE review_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.review_id_seq OWNER TO postgres;

--
-- TOC entry 1859 (class 0 OID 0)
-- Dependencies: 1526
-- Name: review_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE review_id_seq OWNED BY review.id;


--
-- TOC entry 1860 (class 0 OID 0)
-- Dependencies: 1526
-- Name: review_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('review_id_seq', 16, true);


--
-- TOC entry 1531 (class 1259 OID 41068)
-- Dependencies: 5
-- Name: sms; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sms (
    id integer NOT NULL,
    phone character varying(100) NOT NULL,
    current_stamp timestamp without time zone,
    message character varying(1000) NOT NULL,
    code_id integer,
    review_id integer,
    responsible_id integer,
    expected_date date,
    actual_date date,
    resolution character varying(500)
);


ALTER TABLE public.sms OWNER TO postgres;

--
-- TOC entry 1530 (class 1259 OID 41066)
-- Dependencies: 5 1531
-- Name: sms_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE sms_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.sms_id_seq OWNER TO postgres;

--
-- TOC entry 1861 (class 0 OID 0)
-- Dependencies: 1530
-- Name: sms_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE sms_id_seq OWNED BY sms.id;


--
-- TOC entry 1862 (class 0 OID 0)
-- Dependencies: 1530
-- Name: sms_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('sms_id_seq', 66, true);


--
-- TOC entry 1532 (class 1259 OID 42872)
-- Dependencies: 1619 5
-- Name: smsline; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW smsline AS
    SELECT sms.id, sms.phone, sms.current_stamp, sms.message, sms.code_id, sms.review_id, sms.responsible_id, sms.expected_date, sms.actual_date, sms.resolution, getfio(r.firstname, r.lastname, r.middlename) AS fioline, getfullfio(r.firstname, r.lastname, r.middlename) AS fullfioline, c.code, c.shortname, rw.name AS reviewname, getorderstate(sms.expected_date, sms.actual_date) AS ordercode, getstatename(getorderstate(sms.expected_date, sms.actual_date)) AS orderstate FROM (((sms LEFT JOIN responsible r ON ((r.id = sms.responsible_id))) LEFT JOIN review rw ON ((rw.id = sms.review_id))) LEFT JOIN code c ON ((c.id = sms.code_id)));


ALTER TABLE public.smsline OWNER TO postgres;

--
-- TOC entry 1811 (class 2604 OID 39708)
-- Dependencies: 1523 1522 1523
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE code ALTER COLUMN id SET DEFAULT nextval('code_id_seq'::regclass);


--
-- TOC entry 1813 (class 2604 OID 39719)
-- Dependencies: 1525 1524
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE code_href ALTER COLUMN id SET DEFAULT nextval('code_href_id_seq'::regclass);


--
-- TOC entry 1816 (class 2604 OID 41030)
-- Dependencies: 1528 1529 1529
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE responsible ALTER COLUMN id SET DEFAULT nextval('responsible_id_seq'::regclass);


--
-- TOC entry 1815 (class 2604 OID 39766)
-- Dependencies: 1526 1527 1527
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE review ALTER COLUMN id SET DEFAULT nextval('review_id_seq'::regclass);


--
-- TOC entry 1817 (class 2604 OID 41071)
-- Dependencies: 1530 1531 1531
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE sms ALTER COLUMN id SET DEFAULT nextval('sms_id_seq'::regclass);


--
-- TOC entry 1839 (class 0 OID 39705)
-- Dependencies: 1523
-- Data for Name: code; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY code (id, code, name, shortname, "order") FROM stdin;
71	1	Функционирование политической системы и гражданского общества	Функционирование политической системы и гражданского общества	0
82	1.1	Развитие партийной системы	Развитие партийной системы	0
83	1.2	Развитие институтов гражданского общества	Развитие институтов гражданского общества	0
85	1.4	Правозащита	Правозащита	0
86	1.5	Судебная система	Судебная система	0
87	1.6	Армия (призыв, дедовщина, реформы и т.п.)	Армия (призыв, дедовщина, реформы и т.п.)	0
88	1.7	Внешняя политика	Внешняя политика	0
84	1.3	Взаимоотношения с правоохранительными органами	Взаимоотношения с правоохранительными органами	0
89	2	Взаимодействие государства, бизнеса и общества	Взаимодействие государства, бизнеса и общества	0
90	2.1	Коррупция	Коррупция	0
91	2.2	Эффективность работы чиновников	Эффективность работы чиновников	0
92	2.3	Предложения по организации местного самоуправления	Предложения по организации местного самоуправления	0
93	2.4	Охрана труда и деятельность профсоюзов	Охрана труда и деятельность профсоюзов	0
94	2.5	Меры поддержки бизнеса	Меры поддержки бизнеса	0
95	3	Вопросы качества жизни: здоровье, культура, образование	Вопросы качества жизни: здоровье, культура, образование	0
96	3.1	Поддержка материнства и детства	Поддержка материнства и детства	0
97	3.2	Алкоголизм, наркомания, предложения по пропаганде здорового образа жизни	Алкоголизм, наркомания, предложения по пропаганде ЗОЖ	0
98	3.3	Развитие образования	Развитие образования	0
99	3.4	Развитие культуры (театр, кино, музеи, библиотеки, ТВ и т.п.)	Развитие культуры (театр, кино, музеи, библиотеки, ТВ и т.п.)	0
\.


--
-- TOC entry 1840 (class 0 OID 39714)
-- Dependencies: 1524
-- Data for Name: code_href; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY code_href (id, code_id, parent_id) FROM stdin;
48	71	0
59	82	71
60	83	71
61	84	71
62	85	71
63	86	71
64	87	71
65	88	71
66	89	0
67	90	89
68	91	89
69	92	89
70	93	89
71	94	89
72	95	0
73	96	95
74	97	95
75	98	95
76	99	95
\.


--
-- TOC entry 1842 (class 0 OID 41027)
-- Dependencies: 1529
-- Data for Name: responsible; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY responsible (id, lastname, firstname, middlename, phone, note) FROM stdin;
1	Иванов	Петр	Иванович	+7 918 2323 232 23	
25	e1eqweqwe	\N	\N	\N	\N
26	t5gt	\N	b	\N	\N
27	Owjj	jjoij	oijoj	-09-0	\N
28	eee	ee	e	\N	\N
29	888	\N	\N	\N	\N
30	ergwer	ewgwe	wegwe	\N	\N
\.


--
-- TOC entry 1841 (class 0 OID 39763)
-- Dependencies: 1527
-- Data for Name: review; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY review (id, name) FROM stdin;
1	Положительный
2	Отрицательный
\.


--
-- TOC entry 1843 (class 0 OID 41068)
-- Dependencies: 1531
-- Data for Name: sms; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY sms (id, phone, current_stamp, message, code_id, review_id, responsible_id, expected_date, actual_date, resolution) FROM stdin;
63	12	2011-07-18 02:03:32	12	88	1	1	2011-07-17	\N	\N
64	999	2011-07-19 00:00:00	99	85	\N	\N	\N	\N	\N
65	78	2011-07-18 02:59:19	9	96	\N	\N	\N	\N	\N
66	24234	2011-07-18 09:39:10	23432	87	\N	1	\N	\N	\N
\.


--
-- TOC entry 1825 (class 2606 OID 39724)
-- Dependencies: 1524 1524
-- Name: code_href_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY code_href
    ADD CONSTRAINT code_href_pkey PRIMARY KEY (id);


--
-- TOC entry 1822 (class 2606 OID 39710)
-- Dependencies: 1523 1523
-- Name: code_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY code
    ADD CONSTRAINT code_pkey PRIMARY KEY (id);


--
-- TOC entry 1829 (class 2606 OID 41035)
-- Dependencies: 1529 1529
-- Name: responsible_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY responsible
    ADD CONSTRAINT responsible_pkey PRIMARY KEY (id);


--
-- TOC entry 1827 (class 2606 OID 39768)
-- Dependencies: 1527 1527
-- Name: review_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY review
    ADD CONSTRAINT review_pkey PRIMARY KEY (id);


--
-- TOC entry 1834 (class 2606 OID 41076)
-- Dependencies: 1531 1531
-- Name: sms_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sms
    ADD CONSTRAINT sms_pkey PRIMARY KEY (id);


--
-- TOC entry 1818 (class 1259 OID 39711)
-- Dependencies: 1523
-- Name: code_code_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX code_code_idx ON code USING btree (code);


--
-- TOC entry 1819 (class 1259 OID 39712)
-- Dependencies: 1523
-- Name: code_name_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX code_name_idx ON code USING btree (name);


--
-- TOC entry 1820 (class 1259 OID 39760)
-- Dependencies: 1523
-- Name: code_order_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX code_order_idx ON code USING btree ("order");


--
-- TOC entry 1823 (class 1259 OID 39713)
-- Dependencies: 1523
-- Name: code_shortname_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX code_shortname_idx ON code USING btree (shortname);


--
-- TOC entry 1830 (class 1259 OID 42841)
-- Dependencies: 1531
-- Name: sms_actual_date_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX sms_actual_date_idx ON sms USING btree (actual_date);


--
-- TOC entry 1831 (class 1259 OID 42839)
-- Dependencies: 1531
-- Name: sms_current_stamp_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX sms_current_stamp_idx ON sms USING btree (current_stamp);


--
-- TOC entry 1832 (class 1259 OID 42840)
-- Dependencies: 1531
-- Name: sms_expected_date_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX sms_expected_date_idx ON sms USING btree (expected_date);


--
-- TOC entry 1835 (class 2606 OID 42893)
-- Dependencies: 1524 1523 1821
-- Name: code_href_code_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY code_href
    ADD CONSTRAINT code_href_code_id_fkey FOREIGN KEY (code_id) REFERENCES code(id) ON DELETE CASCADE;


--
-- TOC entry 1836 (class 2606 OID 41077)
-- Dependencies: 1531 1523 1821
-- Name: sms_code_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sms
    ADD CONSTRAINT sms_code_id_fkey FOREIGN KEY (code_id) REFERENCES code(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 1837 (class 2606 OID 41082)
-- Dependencies: 1529 1531 1828
-- Name: sms_responsible_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sms
    ADD CONSTRAINT sms_responsible_id_fkey FOREIGN KEY (responsible_id) REFERENCES responsible(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 1838 (class 2606 OID 41087)
-- Dependencies: 1826 1527 1531
-- Name: sms_review_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sms
    ADD CONSTRAINT sms_review_id_fkey FOREIGN KEY (review_id) REFERENCES review(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 1848 (class 0 OID 0)
-- Dependencies: 5
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2011-07-18 13:20:55

--
-- PostgreSQL database dump complete
--

